﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Framework.Library.Core.Config;
using Framework.Library.Core.Data.DataProvider;
using Framework.Library.Core.Provider.Caches;
using Framework.Library.Data.DAL;
using Framework.Library.Data.IDAO;
using Framework.Library.Core.Provider.Log;
using Framework.Library.Core.Data.DataEngine;
using Framework.Library.Core.Utils.Database;

namespace Framework.Library.Data.DAO.SqlServer
{
    [Serializable]
    public class DAOEntity : IDAOEntity
    {
        Configurations cfg = new Configurations();

        public virtual int Insert<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            int retVal = 0;
            if (entity.lst.Count < 1)
                return retVal;
            QueryBuilder builder = new QueryBuilder(entity.Table);
            builder.QueryType = QueryTypes.Insert;            
            foreach (var o in entity.lst)
            {
                if (!entity.Key.Equals(o.Key, StringComparison.InvariantCultureIgnoreCase))
                    builder.AddNameValuePair(o.Key, o.Value);
                else
                    builder.AddNameValuePair(entity.Key, null, DbType.Int32, ParameterDirection.Output);
            }           
            try
            {
                retVal = GeneralConnection.ExecuteNonQuery(CommandType.Text, builder);
                if (retVal > 0)
                {
                    if (Configurations.Cache)
                        Caching.Data.InvalidateRegion(entity.Table);
                }
            }
            catch (Exception exception)
            {
                Logger.Error(builder.Query + " - " + exception.Message);
            }
            return retVal;
        }

        public virtual void Update<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            if (entity.lst.Count < 1)
                return;
            QueryBuilder builder = new QueryBuilder(entity.Table);
            builder.QueryType = QueryTypes.Update;
            foreach (var o in entity.lst)
            {
                if (!entity.Key.Equals(o.Key, StringComparison.InvariantCultureIgnoreCase))
                    builder.AddNameValuePair(o.Key, o.Value);
            }
            builder.AddCondition(entity.Key, entity.lst[entity.Key]);            
            try
            {
                GeneralConnection.ExecuteNonQuery(CommandType.Text, builder);
                if (Configurations.Cache)
                    Caching.Data.InvalidateRegion(entity.Table);
            }
            catch (Exception exception)
            {
                Logger.Error(builder.Query + " - " + exception.Message);
                throw;
            }
        }

        public virtual void UpdateCompound<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            if (entity.lst.Count < 1)
                return;
            QueryBuilder builder = new QueryBuilder(entity.Table);
            builder.QueryType = QueryTypes.UpdateCompound;
            foreach (var o in entity.lst)
            {
                if (!entity.Key.Equals(o.Key, StringComparison.InvariantCultureIgnoreCase))
                    builder.AddNameValuePair(o.Key, o.Value);
            }
            builder.AddCondition(entity.Key, entity.lst[entity.Key]);            
            try
            {
                GeneralConnection.ExecuteNonQuery(CommandType.Text, builder);
                if (Configurations.Cache)
                    Caching.Data.InvalidateRegion(entity.Table);
            }
            catch (Exception exception)
            {
                Logger.Error(builder.Query + " - " + exception.Message);
                throw;
            }
        }

        public virtual void Delete<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            if (entity.lst.Count < 1)
                return;
            QueryBuilder builder = new QueryBuilder(entity.Table);
            builder.QueryType = QueryTypes.Delete;
            if (entity.lst.Count > 0)
            {
                foreach (var s in entity.lst)
                {
                    builder.AddCondition(s.Key, s.Value);
                }                
            }
            
            try
            {
                GeneralConnection.ExecuteNonQuery(CommandType.Text, builder);
                if (Configurations.Cache)
                    Caching.Data.InvalidateRegion(entity.Table);
            }
            catch (Exception exception)
            {
                Logger.Error(builder.Query + " - " + exception.Message);
                throw;
            }
        }

        public virtual TEntityType GetInfo<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            List<TEntityType> retVal = GetList(entity);
            if (retVal.Count > 0)
                return retVal[0];
            return null;
        }

        public virtual List<TEntityType> GetList<TEntityType>(TEntityType entity) where TEntityType : DALEntity
        {
            List<TEntityType> retVal;
            string param = string.Empty;
            QueryBuilder builder = new QueryBuilder(entity.Table);
            if (entity._columns.Count > 0)
            {
                foreach (var o in entity._columns)
                {
                    builder.AddColumn(o.Name, o.TableName, o.Alias);
                }
            }
            if (entity.lst.Count > 0)
            {                
                int i = 0;
                foreach (var o in entity.lst)
                {
                    if (i == 0)
                        param += o.Key + "=" + o.Value.GetHashCode();
                    else
                        param += "&" + o.Key + "=" + o.Value.GetHashCode();
                    builder.AddCondition(o.Key, o.Value);
                    i++;
                }
            }
            if (!string.IsNullOrEmpty(entity.OrderBy))
                builder.AddSortColumn(entity.OrderBy, entity.OrderSort);

            if (Configurations.Cache)
            {
                retVal = Caching.Data.Get<List<TEntityType>>(entity.Table, (builder.Query + "?" + param).GetHashCode().ToString());
                if (retVal != null)
                    return retVal;
            }
            try
            {
                retVal = GeneralConnection.ExecuteList<TEntityType>(CommandType.Text, builder);
                if (retVal.Count > 0)
                {                    
                    if (Configurations.Cache)
                        Caching.Data.Set(entity.Table, retVal, (builder.Query + "?" + param).GetHashCode().ToString());
                    return retVal;
                }
            }
            catch (Exception exception)
            {
                Logger.Error(builder.Query + " - " + exception.Message);
            }
            return new List<TEntityType>();
        }

        public virtual List<TEntityType> GetList<TEntityType>(TEntityType entity, int pageIndex, int pageSize) where TEntityType : DALEntity
        {
            List<TEntityType> retVal;
            string param = string.Empty;
            int bot = (pageIndex - 1) * pageSize;
            int top = (pageIndex * pageSize) + 1;
            if (pageSize <= 0)
                return null;
            var stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT * FROM (SELECT TOP " + top + " ");
            if (entity._columns.Count > 0)
            {
                StringBuilder builder = new StringBuilder();
                foreach (var o in entity._columns)
                {
                    if (builder.Length > 0)
                        builder.Append(", ");
                    builder.Append(o.ToString());
                }
                stringBuilder.Append(builder.ToString());
            }
            else
                stringBuilder.Append("*");
            stringBuilder.Append(", rnum=DENSE_RANK() OVER (ORDER BY ");
            if (string.IsNullOrEmpty(entity.OrderBy))
                stringBuilder.Append(entity.Key + " DESC");
            else
                stringBuilder.Append(entity.OrderBy);
            stringBuilder.Append(") FROM " + Configurations.DATABASEOWNER + entity.Table);
            ArrayList parameters = null;
            if (entity.lst.Count > 0)
            {
                stringBuilder.Append(" WHERE ");
                parameters = new ArrayList();
                int index = 0;
                
                foreach (var o in entity.lst)
                {
                    if (index == 0)
                    {
                        stringBuilder.AppendFormat("[{0}] = @{0}", o.Key);
                        param += o.Key + "=" + o.Value.GetHashCode();
                        index++;
                    }
                    else
                    {
                        stringBuilder.AppendFormat(" AND [{0}] = @{0}", o.Key);
                        param += "&" + o.Key + "=" + o.Value.GetHashCode();
                    }
                    parameters.Add(new[] { o.Key, o.Value });
                }
            }
            if (!string.IsNullOrEmpty(entity.OrderBy))
                stringBuilder.Append(" ORDER BY " + entity.OrderBy);
            stringBuilder.Append(") " + entity.Table + " WHERE " + Configurations.DATABASEOWNER + entity.Table + ".rnum > " + bot);
            if (Configurations.Cache)
            {
                retVal = Caching.Data.Get<List<TEntityType>>(entity.Table, (stringBuilder + "?" + param).GetHashCode().ToString());
                if (retVal != null)
                    return retVal;
            }
            try
            {
                QueryBuilder builder = new QueryBuilder(entity.Table);
                builder.QueryType = QueryTypes.TSql;
                builder.Query = stringBuilder.ToString();
                builder.Parameters = parameters;
                retVal = GeneralConnection.ExecuteList<TEntityType>(CommandType.Text, builder);
                if (retVal.Count > 0)
                {                    
                    if (Configurations.Cache)
                        Caching.Data.Set(entity.Table, retVal, (stringBuilder + "?" + param).GetHashCode().ToString());
                    return retVal;
                }
                    
            }
            catch (Exception exception)
            {
                Logger.Error(stringBuilder + " - " + exception.Message);
            }
            return new List<TEntityType>();
        }
    }
}